﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SpreadsheetLight;
using KSSModel;
using System.IO;
using OfficeOpenXml;

namespace Utilities
{

    public class Excel
    {
        public static void modifyExcel(List<ReportRy> data, List<cashGroupSB> cashAll,
            List<cashGroupSB> cashMonth, 
            List<cateGroupSB> cateA1,
            List<cateGroupSB> cateUser,
            string templatePath, System.Web.HttpResponse response)
        {
            var sl = new SLDocument(templatePath, "Summary_M");
            int RowStart = 2;
            for (int i = 0; i < data.Count; i++)
            {
                sl.SetCellValue(RowStart + i, 1, data[i].sacnameTh);
                var empCountThisMonth = data[i].EMPCountThisMonth;
                sl.SetCellValue(RowStart + i, 3, empCountThisMonth != null ? empCountThisMonth.Value : 0);
                sl.SetCellValue(RowStart + i, 4, "=(C" + (RowStart + i) + "/SUM(C2:C" + (data.Count + RowStart) + "))");
                var empCount = data[i].EMPCount;
                if (empCount != null)
                {
                    sl.SetCellValue(RowStart + i, 5, empCount.Value);
                }
                else
                {
                    sl.SetCellValue(RowStart + i, 5, 0);
                }

                sl.SetCellValue(RowStart + i, 6, "=(C" + (RowStart + i) + "/SUM(E2:E" + (data.Count + RowStart) + "))");

                var doccountThisMonth = data[i].DoccountThisMonth;
                sl.SetCellValue(RowStart + i, 7, doccountThisMonth != null ? doccountThisMonth.Value : 0);
                sl.SetCellValue(RowStart + i, 8, "=(G" + (RowStart + i) + "/C" + (RowStart + i) + ")");
                var doccount = data[i].Doccount;
                sl.SetCellValue(RowStart + i, 9, doccount != null ? doccount.Value : 0);
                sl.SetCellValue(RowStart + i, 10, "=(I" + (RowStart + i) + "/E" + (RowStart + i) + ")");
                sl.SetCellValue(RowStart + i, 11, data[i].CashThisMonth ?? 0);
                sl.SetCellValue(RowStart + i, 12, data[i].Cash ?? 0);
                sl.SetCellValue(RowStart + i, 13, "=(L" + (RowStart + i) + "/F" + (RowStart + i) + ")");
                var moneySaveThisMonth = data[i].moneySaveThisMonth;
                if (moneySaveThisMonth != null)
                    sl.SetCellValue(RowStart + i, 16, moneySaveThisMonth.Value);
                else
                {
                    sl.SetCellValue(RowStart + i, 16,0);
                }
                var moneySave = data[i].moneySave;
                if (moneySave != null) sl.SetCellValue(RowStart + i, 17, moneySave.Value);
                else
                {
                    sl.SetCellValue(RowStart + i, 17, 0);
                }
            }
            sl.SelectWorksheet("OD_ABCDE_ประจำเดือน");
            for (int i = 0; i < cashMonth.Count; i++)
            {
                sl.SetCellValue(2 + i, 1, cashMonth[i].price);
                sl.SetCellValue(2 + i, 2, cashMonth[i].count);
            }
            sl.SelectWorksheet("OD_ABCDE_สะสม");
            for (int i = 0; i < cashAll.Count; i++)
            {
                sl.SetCellValue(2 + i, 1, cashAll[i].price);
                sl.SetCellValue(2 + i, 2, cashAll[i].count);
            }

            var k1 = from c1 in cateA1
                     group c1 by c1.CateName into g
                     select g.Key;
            foreach (var item in k1.ToList())
	        {
		        var c1 = cateA1.Where(c=>c.CateName.Equals(item) && c.isMonthly).ToList();
                sl.AddWorksheet(item + "_สะสม");
                sl.MoveWorksheet(item + "_สะสม", 4);
                sl.SelectWorksheet(item + "_สะสม");
                sl.SetCellValue(1, 1, item);
                sl.SetCellValue(1, 2, "จำนวน");
                for (int i = 0; i < c1.Count(); i++)
                {
                    sl.SetCellValue(2 + i, 1, c1[i].CateItem);
                    sl.SetCellValue(2 + i, 2, c1[i].count);
                }
               
                var c2 = cateA1.Where(c => c.CateName.Equals(item) && !c.isMonthly).ToList();
                sl.AddWorksheet(item + "_ประจำเดือน");
                sl.MoveWorksheet(item + "_ประจำเดือน", 4);
                sl.SelectWorksheet(item + "_ประจำเดือน");
                sl.SetCellValue(1, 1, item);
                sl.SetCellValue(1, 2, "จำนวน");
                for (int i = 0; i < c2.Count(); i++)
                {
                    sl.SetCellValue(2 + i, 1, c1[i].CateItem);
                    sl.SetCellValue(2 + i, 2, c1[i].count);
                }

	        }

            var k2 = from c1 in cateUser
                     group c1 by c1.CateName into g
                     select g.Key;
            foreach (var item in k2.ToList())
            {
                var c1 = cateUser.Where(c => c.CateName.Equals(item) && c.isMonthly).ToList();
                sl.AddWorksheet(item + "_สะสม");
                sl.MoveWorksheet(item + "_สะสม", 4);
                sl.SelectWorksheet(item + "_สะสม");
                sl.SetCellValue(1, 1, item);
                sl.SetCellValue(1, 2, "จำนวน");
                for (int i = 0; i < c1.Count(); i++)
                {
                    sl.SetCellValue(2 + i, 1, c1[i].CateItem);
                    sl.SetCellValue(2 + i, 2, c1[i].count);
                }

                var c2 = cateUser.Where(c => c.CateName.Equals(item) && !c.isMonthly).ToList();
                sl.AddWorksheet(item + "_ประจำเดือน");
                sl.MoveWorksheet(item + "_ประจำเดือน", 4);
                sl.SelectWorksheet(item + "_ประจำเดือน");
                sl.SetCellValue(1, 1, item);
                sl.SetCellValue(1, 2, "จำนวน");
                for (int i = 0; i < c2.Count(); i++)
                {
                    sl.SetCellValue(2 + i, 1, c1[i].CateItem);
                    sl.SetCellValue(2 + i, 2, c1[i].count);
                }

            }
            response.Clear();
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.AddHeader("Content-Disposition", "attachment; filename=ReportTQM.xlsx");
            sl.SaveAs(response.OutputStream);
            
            response.End();
        }

        public static void CreateRYTHExcel(List<ReportRYTechno> data, string templatePath, System.Web.HttpResponse response)
        {
            var sl = new SLDocument(templatePath, "NPI GROUP");
            int RowStart = 3;
            for (int i = 0; i < data.Count; i++)
            {
                sl.SetCellValue(RowStart + i, 1, data[i].Mstaffno);
                sl.SetCellValue(RowStart + i, 2, data[i].MTitle);
                sl.SetCellValue(RowStart + i, 3, data[i].MName);
                sl.SetCellValue(RowStart + i, 4, data[i].MSurname);
                sl.SetCellValue(RowStart + i, 5, data[i].SacnameTh);
                sl.SetCellValue(RowStart + i, 6, data[i].MEmail);
                sl.SetCellValue(RowStart + i, 7, data[i].Month + "-" + data[i].year);
                var doccount = data[i].Doccount;
                sl.SetCellValue(RowStart + i, 8, doccount != null ? doccount.Value : 0);
               
            }
            response.Clear();
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.AddHeader("Content-Disposition", "attachment; filename=ReportTQMSB.xlsx");
            sl.SaveAs(response.OutputStream);

            response.End();
        }


        public static void CreateSBExcel(List<ReportSB> data, string templatePath, System.Web.HttpResponse response)
        {
            var sl = new SLDocument(templatePath, "DATA");
            int RowStart = 3;
            for (int i = 0; i < data.Count; i++)
            {
                sl.SetCellValue(RowStart + i, 2, data[i].MFacName);
                sl.SetCellValue(RowStart + i, 3, data[i].MdepName);
                sl.SetCellValue(RowStart + i, 3, data[i].SacnameTh);
                sl.SetCellValue(RowStart + i, 4, data[i].MdepName);
                sl.SetCellValue(RowStart + i, 6, data[i].MName);
                sl.SetCellValue(RowStart + i, 7, data[i].MSurname);
                sl.SetCellValue(RowStart + i, 8, data[i].Mstaffno);
                var doccount = data[i].Doccount;
                sl.SetCellValue(RowStart + i, 11, doccount != null ? doccount.Value : 0);
                var doccountThisMonth = data[i].DoccountThisMonth;
                sl.SetCellValue(RowStart + i, 12, doccountThisMonth != null ? doccountThisMonth.Value : 0);
                sl.SetCellValue(RowStart + i, 13, data[i].TitleSummary);
            }
            response.Clear();
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.AddHeader("Content-Disposition", "attachment; filename=ReportTQMSB.xlsx");
            sl.SaveAs(response.OutputStream);

            response.End();
        }

        public static void SaveCashExcel(List<tblDoc> ds, string templatePath, System.Web.HttpResponse response)
        {
            const int startInsert = 8;
            var sl = new SLDocument(templatePath, "DATA");
            sl.InsertRow(startInsert, ds.Count);
            for (int i = 0; i < ds.Count; i++)
            {
                sl.SetCellValue(startInsert + i, 18, "เบิกค่า kaizen เรื่อง " + ds[i].title + ":เอกสารเลขที่ " + ds[i].id);
                sl.SetCellValue(startInsert + i, 23, ds[i].cash ?? 0);
            }
            sl.SetCellValue(startInsert + ds.Count, 23, "=SUM(W8:W"+ (startInsert+ds.Count -1) +")");
            sl.SetCellValue(4, 10, ds[0].departmentName);
            sl.SetCellValue(5, 10, ds[0].createdByName);
            sl.SetCellValue(5, 20, ds[0].createdBy);
            sl.SetCellValue(5, 23, DateTime.Today.Day + "/" + DateTime.Today.Month +"/" + DateTime.Today.Year);
            response.Clear();
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.AddHeader("Content-Disposition", "attachment; filename=CashExcel.xlsx");
            sl.SaveAs(response.OutputStream);

            response.End();
        }

        public static List<kssMonthlyTarget> readMonthlyExcel(string filePath)
        {
            List<kssMonthlyTarget> result = new List<kssMonthlyTarget>();
            using (var package = new ExcelPackage(new System.IO.FileInfo(filePath)))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                        var row = currentWorksheet.Dimension.End.Row;
                        for (int i = 3; i <= row; i++)
                        {
                            var sectionName = currentWorksheet.Cells[i, 1].Value.ToString();
                            var year =  currentWorksheet.Cells[2, 2].Value.ToString();
                            DateTime yearCurrent = DateTime.Now;
                            DateTime.TryParse(year,out yearCurrent);

                            for (int month = 1; month <= 12; month++)
                            {
                                var monthlyQty = currentWorksheet.Cells[i, 1 + month].Value.ToString();
                                
                                int monthlyQtyInt = 0;
                                Int32.TryParse(monthlyQty,out monthlyQtyInt);

                                var monthlyTarget = new kssMonthlyTarget()
                                {
                                    SectionName = sectionName,
                                    Target =monthlyQtyInt,
                                    Month = month,
                                    Year = yearCurrent.Year
                                };
                                result.Add(monthlyTarget);
                            }
                        }
                    }
                }
            }
            return result;
        }
        
    }


}
